﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OSISMarkingPeriodBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;

namespace OSISMarkingPeriodDataLayer
{
    /// <summary>
    /// Summary description for MarkingPeriodDL
    /// </summary>
    public class MarkingPeriodDL
    {
       public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
       // public static string server = "MSSQLServer";
        public MarkingPeriodDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public System.Data.DataTable PopulateMarkingPeriodDetails(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SY.years_type_id,SMP.sch_mp_name,SMP.sch_mp_short_nm,SMP.sch_mp_grading_ind,SMP.sch_mp_start_date,SMP.sch_mp_end_date,SMP.sch_mp_grd_start_date,SMP.sch_mp_grd_end_date,SMP.sch_marking_period_id,SMP.sch_marking_period_type from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id inner join year_type YT on SY.years_type_id =YT.years_type_id where SMP.sch_id=" + MPeriod.SchId + " and YT.year_type_cd ='Y'").Tables[0];
            return dt;
        }

        public int InsertintoSchoolYear(MarkingPeriodBL MPeriod)
        {

            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                object id;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");

                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + MPeriod.MPStartDate.Replace("'", "''") + "',N'" + MPeriod.MPEndDate.Replace("'", "''") + "',N'" + MPeriod.SchyrShortName.Replace("'", "''") + "',N'" + MPeriod.SchyrName.Replace("'", "''") + "'," + MPeriod.SchyrYear + ",N'" + MPeriod.LastUpdate.Replace("'", "''") + "'," + MPeriod.SchyrNextYear + "," + MPeriod.SchyrPrevYear + "," + MPeriod.YearTypeId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }
            if (server == "MySQL")
            {

                DataSet ds = null;
                object id;

                //ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('schyr_id','school_years')");

                DateTime s = Convert.ToDateTime(MPeriod.LastUpdate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.LastUpdate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(MPeriod.MPStartDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPStartDate = d.ToString("yyyy/MM/dd");
                DateTime e = Convert.ToDateTime(MPeriod.MPEndDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPEndDate = e.ToString("yyyy/MM/dd");

              


                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + MPeriod.MPStartDate.Replace("'", "''") + "',N'" + MPeriod.MPEndDate.Replace("'", "''") + "',N'" + MPeriod.SchyrShortName.Replace("'", "''") + "',N'" + MPeriod.SchyrName.Replace("'", "''") + "'," + MPeriod.SchyrYear + ",N'" + MPeriod.LastUpdate.Replace("'", "''") + "'," + MPeriod.SchyrNextYear + "," + MPeriod.SchyrPrevYear + "," + MPeriod.YearTypeId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }
            else 
            
            {
                DataSet ds = null;
                object id;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");

                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + MPeriod.MPStartDate.Replace("'", "''") + "',N'" + MPeriod.MPEndDate.Replace("'", "''") + "',N'" + MPeriod.SchyrShortName.Replace("'", "''") + "',N'" + MPeriod.SchyrName.Replace("'", "''") + "'," + MPeriod.SchyrYear + ",N'" + MPeriod.LastUpdate.Replace("'", "''") + "'," + MPeriod.SchyrNextYear + "," + MPeriod.SchyrPrevYear + "," + MPeriod.YearTypeId + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }
            

        }

        public void InsertintoMarkingPeriod(MarkingPeriodBL MPeriod)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_marking_period_id,sch_marking_periods");

                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + MPeriod.MarkingPeriodType.Replace("'", "''") + "',N'" + MPeriod.MPStartDate.Replace("'", "''") + "',N'" + MPeriod.MPEndDate.Replace("'", "''") + "',N'" + MPeriod.MPAttendenceInd.Replace("'", "''") + "',N'" + MPeriod.MPGradeInd.Replace("'", "''") + "',N'" + MPeriod.MPGrdStartDate.Replace("'", "''") + "',N'" + MPeriod.MPGrdEndDate.Replace("'", "''") + "'," + MPeriod.SchId + "," + MPeriod.SchyrId + ",N'" + MPeriod.MarkingPeriodName.Replace("'", "''") + "',N'" + MPeriod.MPShortName.Replace("'", "''") + "',N'" + MPeriod.LastUpdate.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                //ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_marking_period_id,sch_marking_periods");
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('sch_marking_period_id','sch_marking_periods')");

                DateTime s = Convert.ToDateTime(MPeriod.LastUpdate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.LastUpdate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(MPeriod.MPStartDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPStartDate = d.ToString("yyyy/MM/dd");
                DateTime e = Convert.ToDateTime(MPeriod.MPEndDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPEndDate = e.ToString("yyyy/MM/dd");

                DateTime f = Convert.ToDateTime(MPeriod.MPGrdEndDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPGrdEndDate = f.ToString("yyyy/MM/dd");

                DateTime g = Convert.ToDateTime(MPeriod.MPGrdStartDate, DateTimeFormatInfo.InvariantInfo);
                MPeriod.MPGrdStartDate = g.ToString("yyyy/MM/dd");

                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + MPeriod.MarkingPeriodType.Replace("'", "''") + "',N'" + MPeriod.MPStartDate.Replace("'", "''") + "',N'" + MPeriod.MPEndDate.Replace("'", "''") + "',N'" + MPeriod.MPAttendenceInd.Replace("'", "''") + "',N'" + MPeriod.MPGradeInd.Replace("'", "''") + "',N'" + MPeriod.MPGrdStartDate.Replace("'", "''") + "',N'" + MPeriod.MPGrdEndDate.Replace("'", "''") + "'," + MPeriod.SchId + "," + MPeriod.SchyrId + ",N'" + MPeriod.MarkingPeriodName.Replace("'", "''") + "',N'" + MPeriod.MPShortName.Replace("'", "''") + "',N'" + MPeriod.LastUpdate.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
            }
            else
            { 
            
            }

           
           
        }

        public int ReturnMarkingPeriodTypeId(MarkingPeriodBL MPeriod)
        {
            object id;

            id = DataConnection.ConnectionPath.Getdata("select years_type_id from year_type where year_type_cd= N'" + MPeriod.MarkingPeriodType + "'").Tables[0].Rows[0][0];
            return Convert.ToInt32(id);
        }

        public System.Data.DataTable PopulateMarkingPeriodAll(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SY.years_type_id,SMP.sch_mp_name,SMP.sch_mp_short_nm,SMP.sch_mp_grading_ind,SMP.sch_mp_start_date,SMP.sch_mp_end_date,SMP.sch_mp_grd_start_date,SMP.sch_mp_grd_end_date,SMP.sch_marking_period_id,SMP.sch_marking_period_type from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id inner join year_type YT on SY.years_type_id =YT.years_type_id where SMP.sch_id=" + MPeriod.SchId + " and YT.year_type_cd = N'" + MPeriod.MarkingPeriodType + "' and SY.schyr_year=" + MPeriod.SchyrId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable PopulateDetails(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SY.years_type_id,SMP.sch_mp_name,SMP.sch_mp_short_nm,SMP.sch_mp_grading_ind,SMP.sch_mp_start_date,SMP.sch_mp_end_date,SMP.sch_mp_grd_start_date,SMP.sch_mp_grd_end_date,SMP.sch_marking_period_id,SMP.sch_marking_period_type from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id where SMP.sch_id=" + MPeriod.SchId + " and SY.schyr_year =" + MPeriod.SchyrId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable PopulateSchoolYearName(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select schyr_nm,schyr_short_nm from year_type YT inner join school_years SY on YT.years_type_id =SY.years_type_id inner join sch_marking_periods SMP on SY.schyr_id =SMP.schyr_id where YT.year_type_cd ='y' and SMP.sch_id =" + MPeriod.SchId + "").Tables[0];
            return dt;
        }

        public void UpdateMarkingPeriod(MarkingPeriodBL MPeriod)
        {

            try
            {

                if (server == "MSSQLServer")
                {
                    String strSchoolYear = "update school_years set schyr_start_date= N'" + MPeriod.MPStartDate.Replace("'", "''") + "',schyr_end_date= N'" + MPeriod.MPEndDate.Replace("'", "''") + "',last_updated= N'" + MPeriod.LastUpdate.Replace("'", "''") + "',schyr_nm= N'" + MPeriod.SchyrName.Replace("'", "''") + "'  where schyr_id=" + MPeriod.SchyrId + "";
                    String strMarkingPeriod = "update sch_marking_periods set sch_mp_start_date= N'" + MPeriod.MPStartDate.Replace("'", "''") + "',sch_mp_end_date= N'" + MPeriod.MPEndDate.Replace("'", "''") + "',sch_mp_grading_ind= N'" + MPeriod.MPGradeInd + "',sch_mp_grd_start_date= N'" + MPeriod.MPGrdStartDate.Replace("'", "''") + "',sch_mp_grd_end_date= N'" + MPeriod.MPGrdEndDate.Replace("'", "''") + "',sch_mp_name= N'" + MPeriod.MarkingPeriodName.Replace("'", "''") + "',sch_mp_short_nm= N'" + MPeriod.MPShortName.Replace("'", "''") + "',last_updated= N'" + MPeriod.LastUpdate.Replace("'", "''") + "' where sch_marking_period_id=" + MPeriod.MarkingPeriodId + "";

                    DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                    DataConnection.ConnectionPath.ExecuteCommand(strMarkingPeriod);
                }
                if (server == "MySQL")
                {
                    DateTime s = Convert.ToDateTime(MPeriod.LastUpdate, DateTimeFormatInfo.InvariantInfo);
                    MPeriod.LastUpdate = s.ToString("yyyy/MM/dd");

                    DateTime d = Convert.ToDateTime(MPeriod.MPStartDate, DateTimeFormatInfo.InvariantInfo);
                    MPeriod.MPStartDate = d.ToString("yyyy/MM/dd");
                    DateTime e = Convert.ToDateTime(MPeriod.MPEndDate, DateTimeFormatInfo.InvariantInfo);
                    MPeriod.MPEndDate = e.ToString("yyyy/MM/dd");

                    DateTime f = Convert.ToDateTime(MPeriod.MPGrdEndDate, DateTimeFormatInfo.InvariantInfo);
                    MPeriod.MPGrdEndDate = f.ToString("yyyy/MM/dd");

                    DateTime g = Convert.ToDateTime(MPeriod.MPGrdStartDate, DateTimeFormatInfo.InvariantInfo);
                    MPeriod.MPGrdStartDate = g.ToString("yyyy/MM/dd");

                    String strSchoolYear = "update school_years set schyr_start_date= N'" + MPeriod.MPStartDate.Replace("'", "''") + "',schyr_end_date= N'" + MPeriod.MPEndDate.Replace("'", "''") + "',last_updated= N'" + MPeriod.LastUpdate.Replace("'", "''") + "',schyr_nm= N'" + MPeriod.SchyrName.Replace("'", "''") + "'  where schyr_id=" + MPeriod.SchyrId + "";
                    String strMarkingPeriod = "update sch_marking_periods set sch_mp_start_date= N'" + MPeriod.MPStartDate.Replace("'", "''") + "',sch_mp_end_date= N'" + MPeriod.MPEndDate.Replace("'", "''") + "',sch_mp_grading_ind= N'" + MPeriod.MPGradeInd + "',sch_mp_grd_start_date= N'" + MPeriod.MPGrdStartDate.Replace("'", "''") + "',sch_mp_grd_end_date= N'" + MPeriod.MPGrdEndDate.Replace("'", "''") + "',sch_mp_name= N'" + MPeriod.MarkingPeriodName.Replace("'", "''") + "',sch_mp_short_nm= N'" + MPeriod.MPShortName.Replace("'", "''") + "',last_updated= N'" + MPeriod.LastUpdate.Replace("'", "''") + "' where sch_marking_period_id=" + MPeriod.MarkingPeriodId + "";

                    DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                    DataConnection.ConnectionPath.ExecuteCommand(strMarkingPeriod);
                }

               
            }
            catch
            {

            }

        }

        public System.Data.DataTable ReturnSchoolYearId(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select schyr_id,sch_marking_period_id from sch_marking_periods where sch_marking_period_type='Y' and sch_id=" + MPeriod.SchId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable PopulateMarkingPeriodAfterUpdate(int SchId,int SchyrId)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SY.years_type_id,SMP.sch_mp_name,SMP.sch_mp_short_nm,SMP.sch_mp_grading_ind,SMP.sch_mp_start_date,SMP.sch_mp_end_date,SMP.sch_mp_grd_start_date,SMP.sch_mp_grd_end_date,SMP.sch_marking_period_id from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id inner join year_type YT on SY.years_type_id =YT.years_type_id where SMP.sch_id=" + SchId + " and SMP.schyr_id =" + SchyrId + "").Tables[0];
            return dt;
        }

        public System.Data.DataTable PopulateALLMPDetails(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SY.years_type_id,SMP.sch_mp_name,SMP.sch_mp_short_nm,SMP.sch_mp_grading_ind,SMP.sch_mp_start_date,SMP.sch_mp_end_date,SMP.sch_mp_grd_start_date,SMP.sch_mp_grd_end_date,SMP.sch_marking_period_id,SMP.sch_marking_period_type from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id where SMP.sch_id=" + MPeriod.SchId + " and SY.schyr_id =" + MPeriod.SchyrId + "").Tables[0];
            return dt;
        }

        public void DeleteMarkingPeriod(MarkingPeriodBL MPeriod)
        {

            try
            {
                String strMarkingPeriod = "delete from sch_marking_periods where sch_marking_period_id=" + MPeriod.MarkingPeriodId + ";delete from school_years where schyr_id=" + MPeriod.SchyrId + "";
                DataConnection.ConnectionPath.ExecuteCommand(strMarkingPeriod);

               // String strSchoolYear = "delete from school_years where schyr_id=" + MPeriod.SchyrId + "";
                
                //DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                
            }
            catch
            {

            }

        }




        public System.Data.DataTable Populatemarkingperiodforedit(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from sch_marking_periods where sch_marking_period_id=" + MPeriod.MarkingPeriodId + "").Tables[0];
            return dt;
        }
        public System.Data.DataTable getchildcount(MarkingPeriodBL MPeriod)
        {
            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select * from school_years where schyr_year=" + MPeriod.SchyrId + "").Tables[0];
            return dt;
        }
    }
}
